CC1 du 17 octobre 2025 - English translation

Presentation of the database

All the questions concern a hotel database used by a hotel to manage its reservations.

For each relationship (table), we indicate its schema with the type of each attribute in the form attribute_name: type.

By default, NULL values are allowed, unless NOT NULL is specified.

For each table, the values of the numéro attribute are unique, i.e. each row is identified by the value of the numéro attribute.

client( 
    numéro : int (NOT NULL), 
    nom : varchar (NOT NULL), 
    prénom : varchar (NOT NULL), 
    rue : varchar, 
    ville : varchar,
    cp : int, 
    pays : varchar, 
    tel : varchar (NOT NULL), 
    email : vachar
)

chambre( 
    numéro : int (NOT NULL), 
    étage : int (NOT NULL), 
    nblitdouble : int (NOT NULL),
    nblitsimple : int (NOT NULL), 
    description : varchar
)

The attributes nblitdouble and nblitsimple specify the number of double beds and single beds respectively in the room.

réservation( 
    numéro : int (NOT NULL), 
    chambre : int (NOT NULL), 
    client : int (NOT NULL),
    arrivée : date (NOT NULL),
    départ : date (NOT NULL),
    nbadulte : int (NOT NULL), 
    nbenfant : int (NOT NULL)
)

The room attribute refers to the number attribute of the room table.
The client attribute refers to the number attribute of the client table.
The nadult and nchild attributes specify the number of adults and children respectively who will stay in the room.
The date type has the usual order relationship.

Here are some examples of rows for each table:

client
| numéro | nom       | prénom   | rue           | ville     | cp    | pays     |
| 113    | 'Legrand' | 'Claire' | '12 rue d'If' | 'Orléans' | 45000 | 'France' |

 tel          | email                |
 '0610101010' | 'jlegrand@cecher.fr' |

chambre
| numéro | étage | nblitdouble | nblitsimple | description                                |
| 104    | 1     | 1           | 0           | spacieuse, ensoleillée, balcon, WC, douche |

réservation
| numéro | chambre | client | arrivée    | départ     | nbadulte | nbenfant |
| 512    | 104     | 113    | 2025/12/15 | 2025/12/21 | 2        | 0        |

This row in the reservation table stores that customer 113 in the ‘customers’ table (Claire Legrand) has booked room 104 in the ‘room’ table from 2025/12/15 to 2025/12/21 for 2 adults.

Questions

Write in relational algebra and SQL a query that lists the first and last names of customers who live in the city ‘Bordeaux’.

Solution

Write in relational algebra and SQL a query that lists the bookings where the number of people (adults and children) does not respect the number of beds available in the room booked.

Solution

Write in SQL a query that lists the numbers of customers who have no reservations on the 1st floor.

Solution

EXISTS

Write in SQL a query that lists the surnames, first names and number of bookings of the 10 customers who have made the most bookings.
This query looks for cases where two different bookings for the same room have at least one night in common. These are incompatible bookings.

Write in SQL a query which lists, for each incompatibility, the number of the room concerned and the numbers of the two reservations.

Solution

Write in SQL and without subqueries and without INTERSECT, a query that lists the numbers of customers who have at least one reservation for room 101 and at least one reservation for room 303.

Solution

double jointure

Write in SQL with no aggregation function and no subquery, a query that lists the numbers of the rooms that have been booked by at least two different people from the city of ‘Brest’.
Write in SQL with no aggregation function, a query that lists the floors for which all the rooms are reserved on the date ‘2025/12/15’.

Solution

différence